# 1.导入包
import pymysql

host = 'localhost'
port = 3308
user = 'root'
password = '123456'
database = 'test'
charset = 'utf8'
# 2.创建数据库的连接对象，连接数据库
db = pymysql.Connect(host=host, port=port, user=user, password=password, database=database, charset=charset)
print('数据库已连接')
# 3.创建一个数据表
# (1)准备mysql语句
username='zhangsan'
password=123
create_sql="CREATE TABLE qiandaohu (id int PRIMARY key auto_increment,username varchar(10),password int)"
insert_sql=f"INSERT into qiandaohu VALUES(null,'{username}',{password})"
# (2)创建游标对象
curses=db.cursor()
try:
    curses.execute(create_sql)
    db.commit()  # 事务提交
    print('表已创建')
except Exception as e:
    db.rollback()  # 事务回滚
    print(e)

try:
    curses.execute(insert_sql)
    db.commit()  # 事务提交
    print('数据已插入')
except Exception as e:
    db.rollback()  # 事务回滚
    print(e)

curses.close()
db.close()